﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Runtime.Serialization;
using System.Runtime.Serialization.Formatters.Binary;
using System.IO;
using System.Data;
using System.Data.SqlClient;
using System.Transactions;

namespace IEQ_Superintendencia.Services.Persistence
{
    public static class DataUtilsIEQ
    {
        public static string conn = @"Data Source=note-wendel\tabajara;Initial Catalog=IEQSuper;Persist Security Info=True;User ID=sa;Password=delpas";
        //public static string conn = @"DATA SOURCE=orcl;PERSIST SECURITY INFO=True;USER ID=JJ; PASSWORD=pwi";

        public static SqlConnection conection = new SqlConnection(conn);

        //public static void Open()
        //{
        //    if (conection.State == ConnectionState.Closed)
        //        conection.Open();
        //}

        public static DataTable SchemaTable(String tableName)
        {
            DataTable dtRet = new DataTable(tableName);
            List<DataColumn> pks = new List<DataColumn>();

            conection = BDConexao.getInstance().getConnection();

            SqlCommand cmd = new SqlCommand(String.Format("SELECT * FROM {0} WHERE 1=0", tableName), conection);
            cmd.CommandTimeout = 0;
            using (SqlDataReader schemaReader = cmd.ExecuteReader(CommandBehavior.SchemaOnly | CommandBehavior.KeyInfo))
            {
                DataTable dtSchema = schemaReader.GetSchemaTable();
                foreach (DataRow row in dtSchema.Rows)
                {
                    DataColumn dc = null;
                    if (!dtRet.Columns.Contains(row["ColumnName"].ToString()))
                        dc = dtRet.Columns.Add(row["ColumnName"].ToString(), Type.GetType(row["DataType"].ToString()));

                    if ((dc != null) && (Convert.ToBoolean(row["IsKey"].ToString())))
                        pks.Add(dc);
                }

                if (pks.Count != 0)
                    dtRet.PrimaryKey = pks.ToArray();
                return dtRet;
            }
        }

        public static DataTable Select(String tableName, String sqlText)
        {
            using (SqlDataAdapter dataAdapter = new SqlDataAdapter(sqlText, conection))
            {
                dataAdapter.SelectCommand = new SqlCommand(sqlText, conection);
                dataAdapter.SelectCommand.CommandType = CommandType.Text;
                DataTable dtRet = SchemaTable(tableName);
                dataAdapter.Fill(dtRet);
                return dtRet;
            }
        }

        public static object ExecuteScalar(String sqlText)
        {
            return ExecuteScalar(sqlText, false);
        }

        public static object ExecuteScalar(String sqlText, Boolean newConnection)
        {
            SqlConnection localConnection = new SqlConnection(conn);
            if (!newConnection)
                conection = BDConexao.getInstance().getConnection();
            else
                localConnection.Open();
            SqlCommand cmd = new SqlCommand(sqlText, newConnection ? localConnection : conection);
            cmd.CommandTimeout = 0;
            return cmd.ExecuteScalar();
        }

        public static object Execute(String sqlText)
        {
            return Execute(sqlText, false);
        }

        public static int Execute(String sqlText, Boolean newConnection)
        {
            SqlConnection localConnection = new SqlConnection(conn);
            if (!newConnection)
                conection = BDConexao.getInstance().getConnection();
            else
                localConnection.Open();
            SqlCommand cmd = new SqlCommand(sqlText, newConnection ? localConnection : conection);
            cmd.CommandTimeout = 0;
            return cmd.ExecuteNonQuery();
        }

        public static object NextVal(String tableName, String columnName)
        {
            Object ret = ExecuteScalar(String.Format("SELECT MAX(SEQ_CURRENT_VALUE) FROM SEQUENCES_TABLE WHERE SEQ_NAME = 'SEQ_{0}_{1}'", tableName, columnName), true);
            if (ret == DBNull.Value)
            {
                ret = ExecuteScalar(String.Format("SELECT MAX({0}) FROM {1}", columnName, tableName), true);
                if (ret == DBNull.Value)
                    ret = 1;
                Execute(String.Format("INSERT INTO SEQUENCES_TABLE (SEQ_NAME, SEQ_CURRENT_VALUE) VALUES ('SEQ_{0}_{1}', {2})", tableName, columnName, ret), true);
            }
            else
            {
                ret = Convert.ToInt64(ret) + 1;
                Execute(String.Format("UPDATE SEQUENCES_TABLE SET SEQ_CURRENT_VALUE = {0} WHERE SEQ_NAME = 'SEQ_{1}_{2}'", ret, tableName, columnName), true);
            }
            return ret;
        }

        private static String ValuesPKError(DataTable dt, DataRow row)
        {
            if (dt.PrimaryKey.Length > 0)
            {
                StringBuilder valuePkErro = new StringBuilder();

                foreach (DataColumn dc in dt.PrimaryKey)
                {
                    if (valuePkErro.Length <= 0)
                        valuePkErro.AppendFormat("[{0}: {1}", dc.ColumnName, row[dc.ColumnName]);
                    else
                        valuePkErro.AppendFormat(" - {0}: {1}", dc.ColumnName, row[dc.ColumnName]);
                }
                valuePkErro.Append("]");
                return "Chave da Tabela: " + valuePkErro.ToString();
            }
            return String.Empty;
        }

        private static void BuildMsgErrors(DataTable dtSave)
        {
            if (dtSave.HasErrors)
            {
                StringBuilder log = new StringBuilder();
                DataRow[] dr = dtSave.GetErrors();
                Int32 sumErrors = dr.Length;
                if (sumErrors > 5)
                    sumErrors = 5;
                for (Int32 i = 0; i <= sumErrors - 1; i++)
                    log.AppendLine(String.Format("{0} => Erro ao salvar registro: ({1})", ValuesPKError(dtSave, dr[i]), dr[i].RowError));
                log.AppendLine("Total de erros gerados: " + dr.Length.ToString());
                log.AppendLine("ROLLBACK realizado para bloco processado");
                throw new Exception("Transação foi abortada" + Environment.NewLine + log.ToString());
            }
        }

        private static void SetPrimaryKeyValues(DataTable dtCurrent)
        {
            if (dtCurrent.PrimaryKey.Length != 1)
                throw new Exception("A PRIMARY KEY da tabela: " + dtCurrent.TableName + " não é válida. Ou é composta ou não existe primary definida");

            foreach (DataRow row in dtCurrent.Rows)
            {
                if ((dtCurrent.PrimaryKey[0].DataType == typeof(System.Int32) || dtCurrent.PrimaryKey[0].DataType == typeof(System.Int16) ||
                    dtCurrent.PrimaryKey[0].DataType == typeof(System.Int64) || dtCurrent.PrimaryKey[0].DataType == typeof(System.Decimal))
                    && row.RowState == DataRowState.Added
                    && Convert.ToInt64(row[dtCurrent.PrimaryKey[0].ColumnName]) < 0)
                {
                    row[dtCurrent.PrimaryKey[0].ColumnName] = NextVal(dtCurrent.TableName, dtCurrent.PrimaryKey[0].ColumnName);
                }
            }
        }

        public static bool BatchData(DataSet dsOriginal, Boolean verifyPrimaryKey)
        {
            if (verifyPrimaryKey)
                foreach (DataTable dt in dsOriginal.Tables)
                    if (dt.GetChanges() != null)
                        SetPrimaryKeyValues(dt);
            return BatchData(dsOriginal);
        }

        public static bool BatchData(DataSet dsOriginal)
        {
            using (TransactionScope tranScope = new TransactionScope())
            {
                DataSet dsRet = dsOriginal.Clone();
                foreach (DataTable dtSave in dsOriginal.Tables)
                {
                    if (dtSave.GetChanges() != null)
                    {
                        DataTable dtClone = dtSave.Clone();
                        dtClone.Merge(dtSave);
                        BatchData(dtClone, dtClone.TableName, 50);
                        BuildMsgErrors(dtClone);
                        dsRet.Tables.Add(dtClone);
                    }
                }
                //dar o commit na transação corrente
                tranScope.Complete();
                dsRet.AcceptChanges();
                dsOriginal = dsRet;
            }
            return dsOriginal.HasErrors;
        }

        public static bool BatchData(DataTable dtOriginal, String tableName, Int32 batchSize)
        {
            String sqlText = String.Format("SELECT * FROM {0} where 1=0", tableName);

            using (SqlDataAdapter dataAdapter = new SqlDataAdapter(sqlText, conection))
            {
                dataAdapter.SelectCommand = new SqlCommand(sqlText, conection);
                dataAdapter.SelectCommand.CommandType = CommandType.Text;

                //pegar a estrutura da tabela do banco de dados
                dtOriginal.Merge(Select(tableName, sqlText), true, MissingSchemaAction.AddWithKey);

                SqlCommandBuilder commonBuilder = new SqlCommandBuilder(dataAdapter);

                if (dtOriginal.PrimaryKey.Length > 0)
                {
                    commonBuilder.SetAllValues = false;
                    dataAdapter.UpdateCommand = commonBuilder.GetUpdateCommand();
                    dataAdapter.UpdateCommand.Connection = conection;
                }
                else
                {
                    DataView vw = new DataView(dtOriginal);
                    vw.RowStateFilter = DataViewRowState.ModifiedCurrent;
                    if (vw.Count != 0)
                        throw new Exception(String.Format("Infome as PrimaryKey do DataTable: {0}", dtOriginal.TableName));
                    else
                    {
                        vw.RowStateFilter = DataViewRowState.ModifiedOriginal;
                        if (vw.Count != 0)
                            throw new Exception(String.Format("Infome as PrimaryKey do DataTable: {0}", dtOriginal.TableName));
                    }
                }
                dataAdapter.InsertCommand = commonBuilder.GetInsertCommand();
                dataAdapter.InsertCommand.Connection = conection;

                if (dataAdapter.UpdateCommand != null)
                    dataAdapter.UpdateCommand.CommandTimeout = 0;
                if (dataAdapter.InsertCommand != null)
                    dataAdapter.InsertCommand.CommandTimeout = 0;
                if (dataAdapter.DeleteCommand != null)
                    dataAdapter.DeleteCommand.CommandTimeout = 0;

                dataAdapter.ContinueUpdateOnError = true;
                dataAdapter.UpdateBatchSize = batchSize;
                dataAdapter.Update(dtOriginal);

                if (dtOriginal.GetErrors().Length == 0)
                    return true;
                else
                    return false;
            }
        }
    }
}
